package cs2340.todolist.model.sqlite_database;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import cs2340.todolist.model.Location;
import cs2340.todolist.model.ToDoItem;
import cs2340.todolist.model.User;
import cs2340.todolist.model.account_management.ToDoItemManager;

/**
 * providing the protocol to communicate with the database
 * @author BaoTam
 *
 */
public class ToDoItemDataSource {
	
	// Database fields
	private SQLiteDatabase database;
	private ToDoItemSQLiteHelper dbHelper;
	private Context context;
	
	/**
	 * instantiate the datasource that holds a db helper 
	 * @param context
	 */
	public ToDoItemDataSource(Context context) {
		setContext(context);
	}
	
	/**
	 * open the database 
	 * @throws SQLException
	 */
	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}
	
	/**
	 * close the database
	 */
	public void close() {
		dbHelper.close();
	}
	
	/**
	 * parses a date
	 * @param dateStr - string code that contains a date
	 * @return - the date generated from the string code
	 */
	public Date parseDate(String dateStr) {
		Date date = new Date(
				Integer.parseInt(dateStr.substring(0, 4)), //year
				Integer.parseInt(dateStr.substring(4,6))-1, //month
				Integer.parseInt(dateStr.substring(6,8)), //day
				Integer.parseInt(dateStr.substring(8,10)), //hours
				Integer.parseInt(dateStr.substring(10,12)) //mins
				);

		return date;
	}
	
	/**
	 * encode a date to yyyymmddhh''
	 * @param date
	 * @return the string of the encoded date
	 */
	public String encodeDate(Date date) {
		String ret = "";
		ret += String.format("%04d",date.getYear());
		ret += String.format("%02d",date.getMonth()+1);
		ret += String.format("%02d",date.getDate());
		ret += String.format("%02d",(date.getHours()%12));
		ret += String.format("%02d",date.getMinutes());
		return ret;
	}
	
	/**
	 * retrieve the fields from the cursor to build a speakUpMessage
	 * @param cursor
	 * @return the constructed speakUpMessage
	 */
	private ToDoItem cursorToItem(Cursor cursor) {
		//String title, Location location, String type, Date dueDate, String recurType, String description
		ToDoItem toDoItem = new ToDoItem(
				cursor.getLong(cursor.getColumnIndex(ToDoItemSQLiteHelper.COLUMN_ID)),
				cursor.getString(cursor.getColumnIndex(ToDoItemSQLiteHelper.COLUMN_TITLE)),
				new Location(cursor.getString(cursor.getColumnIndex(ToDoItemSQLiteHelper.COLUMN_LOCATION_NAME)),cursor.getString(cursor.getColumnIndex(ToDoItemSQLiteHelper.COLUMN_LOCATION_ADDR))),
				ToDoItemManager.TYPES[cursor.getInt(cursor.getColumnIndex(ToDoItemSQLiteHelper.COLUMN_TYPE))],
				parseDate(cursor.getString(cursor.getColumnIndex(ToDoItemSQLiteHelper.COLUMN_DUE_DATE))),
				ToDoItemManager.RECUR_TYPES[cursor.getInt(cursor.getColumnIndex(ToDoItemSQLiteHelper.COLUMN_RECUR_TYPE))],
				cursor.getString(cursor.getColumnIndex(ToDoItemSQLiteHelper.COLUMN_DESCRIPTION)),
				parseBool(cursor.getString(cursor.getColumnIndex(ToDoItemSQLiteHelper.COLUMN_COMPLETED)))
				);
		System.out.println("cursorToItem: " + toDoItem.getTitle() + " \tlocation: " + toDoItem.getLocation().getAddr());
		return toDoItem;
	}
	
	public static boolean parseBool(String str) {
		return str.equals("1");
	}
	
	
	/**
	 * insert an item to the database
	 * @param userID, has to be an id existing in the database
	 * @param title, can neither be blank nor null
	 * @param location
	 * @param type, has to be the one of the indices of the TYPES array in ToDoItemManager
	 * @param dueDate
	 * @param recurType, has to be the one of the indices of the RECUR_TYPES array in ToDoItemManager
	 * @param description
	 * @param completed
	 * @return the id of the new item, -1 if fail to insert the new item
	 */
	public long insertItem(long userID, String title, Location location, int type, Date dueDate, int recurType, String description, boolean completed) {
			System.out.println("trying to insert " + title + "location: " + location.getName() + location.getAddr());
			//whitebox test
			if(title == "" || title == null) {
				return -1;
			}
			if(type >= ToDoItemManager.TYPES.length || type < 0) {
				return -1;
			}
			if(recurType >= ToDoItemManager.RECUR_TYPES.length || recurType < 0) {
				return -1;
			}
			
			UserAccountSQLiteHelper userAccountDbHelper = new UserAccountSQLiteHelper(getContext());
			Cursor cursor = userAccountDbHelper.getWritableDatabase().query(
					UserAccountSQLiteHelper.TABLE_USERS,
					UserAccountSQLiteHelper.getAllColumnNames(),
					UserAccountSQLiteHelper.COLUMN_ID + " = '" + userID + "'",
					null, null, null, null
					);
			if(cursor.isAfterLast()) {
				cursor.close();
				userAccountDbHelper.close();
				return -1;
			}
			cursor.close();
			userAccountDbHelper.close();

			ContentValues toInsertValues = new ContentValues();
			toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_USER_ID, userID);
			toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_TITLE, title);
			toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_TYPE, type);
			toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_DUE_DATE, encodeDate(dueDate));
			toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_RECUR_TYPE, recurType);
			toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_LOCATION_ADDR, location.getAddr());
			toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_LOCATION_NAME, location.getName());
			toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_DESCRIPTION, description);	
			toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_COMPLETED, completed);	
			
			try {
				// To show how to insert, the insert method returns the id of the newly-inserted row
				return database.insert(ToDoItemSQLiteHelper.TABLE_ITEMS, null, toInsertValues);
				
			}
			catch(SQLException ex) {
				ex.printStackTrace();
				return -1;
			}
	}
	
	public int updateItem(long itemId, long userID, String title, Location location, int type, Date dueDate, int recurType, String description, boolean completed) {
		System.out.println("trying to insert " + title);
		//whitebox test
		if(title == "" || title == null) {
			return 0;
		}
		if(type >= ToDoItemManager.TYPES.length || type < 0) {
			return 0;
		}
		if(recurType >= ToDoItemManager.RECUR_TYPES.length || recurType < 0) {
			return 0;
		}
		
		UserAccountSQLiteHelper userAccountDbHelper = new UserAccountSQLiteHelper(getContext());
		Cursor cursor = userAccountDbHelper.getWritableDatabase().query(
				UserAccountSQLiteHelper.TABLE_USERS,
				UserAccountSQLiteHelper.getAllColumnNames(),
				UserAccountSQLiteHelper.COLUMN_ID + " = '" + userID + "'",
				null, null, null, null
				);
		if(cursor.isAfterLast()) {
			cursor.close();
			userAccountDbHelper.close();
			return 0;
		}
		cursor.close();
		userAccountDbHelper.close();

		ContentValues toInsertValues = new ContentValues();
		toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_USER_ID, userID);
		toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_TITLE, title);
		toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_TYPE, type);
		toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_DUE_DATE, encodeDate(dueDate));
		toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_RECUR_TYPE, recurType);
		toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_LOCATION_ADDR, location.getAddr());
		toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_LOCATION_NAME, location.getName());
		toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_DESCRIPTION, description);	
		toInsertValues.put(ToDoItemSQLiteHelper.COLUMN_COMPLETED, completed);	
		
		try {
			// To show how to insert, the insert method returns the id of the newly-inserted row
			return database.update(ToDoItemSQLiteHelper.TABLE_ITEMS, toInsertValues, ToDoItemSQLiteHelper.COLUMN_ID + "=" + itemId, null);
			
		}
		catch(SQLException ex) {
			ex.printStackTrace();
			return 0;
		}
	}
	
	/**
	 * Delete the toDoItem with the specified id from the database
	 * @param id, has to have a value greater than -1
	 * @return true if the deletion has occurred, false otherwise
	 */
	public boolean deleteItem(long id) {
		if(id <= -1) {
			return false;
		}
		int rowsAffected = database.delete(
				ToDoItemSQLiteHelper.TABLE_ITEMS,
				ToDoItemSQLiteHelper.COLUMN_ID + " = " + id,
				null
				);
		return (rowsAffected == 0 ? false : true);
	}
	
	
	
	/**
	 * 
	 * @param id
	 * @return the toDoItem that has the specified id, null if nothing was found
	 */
	public ToDoItem getItemById(long id) {
		 Cursor cursor = database.query(
                 ToDoItemSQLiteHelper.TABLE_ITEMS, 
                 ToDoItemSQLiteHelper.getAllColumnNames(), 
                 ToDoItemSQLiteHelper.COLUMN_ID + " = '" + id + "'",
                 null, null, null, null
                 );
		 if(cursor.isAfterLast()) {
			 return null;
		 }
		 else {
			 cursor.moveToFirst();
			 return cursorToItem(cursor);
		 }
		 
	}
    /**
     * @return a List containing all items for the user with the userId
     */
    public List<ToDoItem> getItemsByUserId(long userId) {
            List<ToDoItem> itemList = new ArrayList<ToDoItem>();
            
            //the following gets the cursor, a container that holds 
            //a number of columns and rows of the speak_up_messages table. 
            //In this case, we want the whole table
            Cursor cursor = database.query(
                            ToDoItemSQLiteHelper.TABLE_ITEMS, 
                            ToDoItemSQLiteHelper.getAllColumnNames(), 
                            ToDoItemSQLiteHelper.COLUMN_USER_ID + " = '" + userId + "'",
                            null, null, null, null
                            );
            
            //go to the first row in the cursor
            cursor.moveToFirst();

            //loop the cursor until hitting the last row in the cursor and add each row to the result list
            while (!cursor.isAfterLast()) {
                    itemList.add(cursorToItem(cursor));
                    cursor.moveToNext();
            }
            cursor.close();//close cursor to free memory space
            
            return itemList;
    }
    
    /**
     * 
     * @return the current context
     */
	public Context getContext() {
		return context;
	}
	
	/**
	 * renew the database helper with the new context
	 * @param context to replace the old one
	 */
	public void setContext(Context context) {
		this.context = context;
		dbHelper = new ToDoItemSQLiteHelper(context);
	}
	
	/**
	 * @return a List containing all the speakUpMessages in the database
	 */
    //I dont think we need this but I don't want to delete it right now
/*	public List<Item> getAllItems() {
		List<Item> userList = new ArrayList<Item>();
		
		//the following gets the cursor, a container that holds 
		//a number of columns and rows of the speak_up_messages table. 
		//In this case, we want the whole table
		Cursor cursor = database.query(
				ToDoItemSQLiteHelper.TABLE_USERS, 
				ToDoItemSQLiteHelper.getAllColumnNames(), 
				null, null, null, null, null
				);
		
		//go to the first row in the cursor
		cursor.moveToFirst();

		//loop the cursor until hitting the last row in the cursor and add each row to the result list
		while (!cursor.isAfterLast()) {
			userList.add(cursorToItem(cursor));
			cursor.moveToNext();
		}
		cursor.close();//close cursor to free memory space
		
		return userList;
	}*/
	
	
}